const db = require('../config/database');
const { logger } = require('../utils/logger');

class SalesReportController {
  // 5. 每日销售摘要 (Daily Sales Summary)
  async getDailySalesSummaryReport(filters = {}) {
    try {
      const {
        start_date = new Date().toISOString().split('T')[0],
        end_date = new Date().toISOString().split('T')[0],
        store_id,
        order_type,
        compare_period = false
      } = filters;

      let whereClause = 'WHERE DATE(o.created_at) BETWEEN ? AND ?';
      let params = [start_date, end_date];

      if (store_id) {
        whereClause += ' AND o.store_id = ?';
        params.push(store_id);
      }
      if (order_type) {
        whereClause += ' AND o.order_type = ?';
        params.push(order_type);
      }

      const sql = `
        SELECT 
          DATE(o.created_at) as date,
          -- 营业额
          SUM(CASE WHEN o.payment_status = 'completed' THEN o.total_amount ELSE 0 END) as revenue,
          -- 净销售 (扣除退款)
          SUM(CASE WHEN o.payment_status = 'completed' THEN o.total_amount ELSE 0 END) - 
          SUM(CASE WHEN o.status = 'refunded' THEN o.total_amount ELSE 0 END) as net_sales,
          -- 毛利 (假设毛利率60%)
          (SUM(CASE WHEN o.payment_status = 'completed' THEN o.total_amount ELSE 0 END) - 
           SUM(CASE WHEN o.status = 'refunded' THEN o.total_amount ELSE 0 END)) * 0.6 as gross_profit,
          -- 订单数
          COUNT(CASE WHEN o.payment_status = 'completed' THEN 1 END) as order_count,
          -- 客单价
          AVG(CASE WHEN o.payment_status = 'completed' THEN o.total_amount END) as avg_order_value,
          -- 会员订单数
          COUNT(CASE WHEN o.payment_status = 'completed' AND o.user_id IS NOT NULL THEN 1 END) as member_orders,
          -- 会员占比
          ROUND(COUNT(CASE WHEN o.payment_status = 'completed' AND o.user_id IS NOT NULL THEN 1 END) * 100.0 / 
                NULLIF(COUNT(CASE WHEN o.payment_status = 'completed' THEN 1 END), 0), 2) as member_ratio,
          -- 订单结构
          COUNT(CASE WHEN o.payment_status = 'completed' AND o.order_type = 'dine_in' THEN 1 END) as dine_in_orders,
          COUNT(CASE WHEN o.payment_status = 'completed' AND o.order_type = 'takeaway' THEN 1 END) as takeaway_orders,
          COUNT(CASE WHEN o.payment_status = 'completed' AND o.order_type = 'delivery' THEN 1 END) as delivery_orders,
          -- 退款订单数
          COUNT(CASE WHEN o.status = 'refunded' THEN 1 END) as refund_orders,
          -- 退款率
          ROUND(COUNT(CASE WHEN o.status = 'refunded' THEN 1 END) * 100.0 / 
                NULLIF(COUNT(*), 0), 2) as refund_rate
        FROM orders o
        ${whereClause}
        GROUP BY DATE(o.created_at)
        ORDER BY date DESC
      `;

      const data = await db.query(sql, params);

      // 获取Top商品
      const topProductsSql = `
        SELECT 
          mi.name as product_name,
          SUM(oi.quantity) as total_quantity,
          SUM(oi.total_price) as total_sales
        FROM orders o
        JOIN order_items oi ON o.id = oi.order_id
        JOIN menu_items mi ON oi.menu_item_id = mi.id
        ${whereClause}
        AND o.payment_status = 'completed'
        GROUP BY mi.id, mi.name
        ORDER BY total_sales DESC
        LIMIT 10
      `;
      const topProducts = await db.query(topProductsSql, params);

      // 获取Top时段
      const topHoursSql = `
        SELECT 
          HOUR(o.created_at) as hour,
          SUM(CASE WHEN o.payment_status = 'completed' THEN o.total_amount ELSE 0 END) as hourly_sales,
          COUNT(CASE WHEN o.payment_status = 'completed' THEN 1 END) as hourly_orders
        FROM orders o
        ${whereClause}
        GROUP BY HOUR(o.created_at)
        ORDER BY hourly_sales DESC
        LIMIT 5
      `;
      const topHours = await db.query(topHoursSql, params);

      // 计算同比/环比数据
      let comparison = null;
      if (compare_period) {
        const daysDiff = Math.ceil((new Date(end_date) - new Date(start_date)) / (1000 * 60 * 60 * 24)) + 1;
        const compareStartDate = new Date(new Date(start_date).getTime() - daysDiff * 24 * 60 * 60 * 1000);
        const compareEndDate = new Date(new Date(end_date).getTime() - daysDiff * 24 * 60 * 60 * 1000);
        
        const compareSql = sql.replace(whereClause, 
          `WHERE DATE(o.created_at) BETWEEN '${compareStartDate.toISOString().split('T')[0]}' AND '${compareEndDate.toISOString().split('T')[0]}'`
        );
        const compareData = await db.query(compareSql, []);
        
        const currentTotal = data.reduce((sum, row) => sum + parseFloat(row.net_sales || 0), 0);
        const compareTotal = compareData.reduce((sum, row) => sum + parseFloat(row.net_sales || 0), 0);
        
        comparison = {
          current_period: currentTotal,
          compare_period: compareTotal,
          growth_rate: compareTotal > 0 ? ((currentTotal - compareTotal) / compareTotal * 100).toFixed(2) : 0,
          growth_amount: currentTotal - compareTotal
        };
      }

      const summary = {
        total_revenue: data.reduce((sum, row) => sum + parseFloat(row.revenue || 0), 0),
        total_net_sales: data.reduce((sum, row) => sum + parseFloat(row.net_sales || 0), 0),
        total_gross_profit: data.reduce((sum, row) => sum + parseFloat(row.gross_profit || 0), 0),
        total_orders: data.reduce((sum, row) => sum + parseInt(row.order_count || 0), 0),
        avg_order_value: 0,
        total_member_orders: data.reduce((sum, row) => sum + parseInt(row.member_orders || 0), 0),
        overall_member_ratio: 0,
        total_refund_orders: data.reduce((sum, row) => sum + parseInt(row.refund_orders || 0), 0),
        overall_refund_rate: 0
      };

      if (summary.total_orders > 0) {
        summary.avg_order_value = (summary.total_net_sales / summary.total_orders).toFixed(2);
        summary.overall_member_ratio = (summary.total_member_orders / summary.total_orders * 100).toFixed(2);
        summary.overall_refund_rate = (summary.total_refund_orders / summary.total_orders * 100).toFixed(2);
      }

      return {
        data,
        summary,
        top_products: topProducts,
        top_hours: topHours,
        comparison,
        charts: [
          {
            type: 'line',
            title: '每日销售趋势',
            data: data.map(row => ({
              x: row.date,
              y: parseFloat(row.net_sales || 0)
            }))
          },
          {
            type: 'bar',
            title: '订单类型分布',
            data: [
              { name: '堂食', value: data.reduce((sum, row) => sum + parseInt(row.dine_in_orders || 0), 0) },
              { name: '外带', value: data.reduce((sum, row) => sum + parseInt(row.takeaway_orders || 0), 0) },
              { name: '外卖', value: data.reduce((sum, row) => sum + parseInt(row.delivery_orders || 0), 0) }
            ]
          }
        ]
      };
    } catch (error) {
      logger.error('生成每日销售摘要报表失败:', error);
      throw error;
    }
  }

  // 6. 小时经营分析 (Hourly Analysis Report)
  async getHourlyAnalysisReport(filters = {}) {
    try {
      const {
        start_date = new Date().toISOString().split('T')[0],
        end_date = new Date().toISOString().split('T')[0],
        store_id
      } = filters;

      let whereClause = 'WHERE DATE(o.created_at) BETWEEN ? AND ?';
      let params = [start_date, end_date];

      if (store_id) {
        whereClause += ' AND o.store_id = ?';
        params.push(store_id);
      }

      const sql = `
        SELECT 
          HOUR(o.created_at) as hour,
          DATE(o.created_at) as date,
          -- 营业额
          SUM(CASE WHEN o.payment_status = 'completed' THEN o.total_amount ELSE 0 END) as hourly_revenue,
          -- 订单数
          COUNT(CASE WHEN o.payment_status = 'completed' THEN 1 END) as hourly_orders,
          -- 客单价
          AVG(CASE WHEN o.payment_status = 'completed' THEN o.total_amount END) as hourly_avg_order,
          -- 出餐时长 (模拟数据，实际应该从订单状态变更记录获取)
          AVG(CASE WHEN o.payment_status = 'completed' THEN 15 + (RAND() * 10) END) as avg_preparation_time,
          -- 人效 (假设每小时2个员工)
          SUM(CASE WHEN o.payment_status = 'completed' THEN o.total_amount ELSE 0 END) / 2 as staff_efficiency,
          -- 厨效 (假设每小时1个厨师)
          COUNT(CASE WHEN o.payment_status = 'completed' THEN 1 END) / 1 as kitchen_efficiency,
          -- 会员占比
          ROUND(COUNT(CASE WHEN o.payment_status = 'completed' AND o.user_id IS NOT NULL THEN 1 END) * 100.0 / 
                NULLIF(COUNT(CASE WHEN o.payment_status = 'completed' THEN 1 END), 0), 2) as member_ratio,
          -- 渠道结构
          COUNT(CASE WHEN o.payment_status = 'completed' AND o.order_type = 'dine_in' THEN 1 END) as dine_in_count,
          COUNT(CASE WHEN o.payment_status = 'completed' AND o.order_type = 'takeaway' THEN 1 END) as takeaway_count,
          COUNT(CASE WHEN o.payment_status = 'completed' AND o.order_type = 'delivery' THEN 1 END) as delivery_count
        FROM orders o
        ${whereClause}
        GROUP BY HOUR(o.created_at), DATE(o.created_at)
        ORDER BY date DESC, hour
      `;

      const data = await db.query(sql, params);

      // 按小时汇总
      const hourlySummary = {};
      data.forEach(row => {
        const hour = row.hour;
        if (!hourlySummary[hour]) {
          hourlySummary[hour] = {
            hour,
            total_revenue: 0,
            total_orders: 0,
            avg_order_value: 0,
            avg_preparation_time: 0,
            staff_efficiency: 0,
            kitchen_efficiency: 0,
            days_count: 0
          };
        }
        
        hourlySummary[hour].total_revenue += parseFloat(row.hourly_revenue || 0);
        hourlySummary[hour].total_orders += parseInt(row.hourly_orders || 0);
        hourlySummary[hour].avg_preparation_time += parseFloat(row.avg_preparation_time || 0);
        hourlySummary[hour].staff_efficiency += parseFloat(row.staff_efficiency || 0);
        hourlySummary[hour].kitchen_efficiency += parseFloat(row.kitchen_efficiency || 0);
        hourlySummary[hour].days_count += 1;
      });

      // 计算平均值
      Object.values(hourlySummary).forEach(hour => {
        if (hour.days_count > 0) {
          hour.avg_order_value = hour.total_orders > 0 ? (hour.total_revenue / hour.total_orders).toFixed(2) : 0;
          hour.avg_preparation_time = (hour.avg_preparation_time / hour.days_count).toFixed(1);
          hour.staff_efficiency = (hour.staff_efficiency / hour.days_count).toFixed(2);
          hour.kitchen_efficiency = (hour.kitchen_efficiency / hour.days_count).toFixed(1);
        }
      });

      // 找出高峰时段
      const peakHours = Object.values(hourlySummary)
        .sort((a, b) => b.total_revenue - a.total_revenue)
        .slice(0, 3)
        .map(hour => ({
          hour: hour.hour,
          revenue: hour.total_revenue,
          orders: hour.total_orders
        }));

      const summary = {
        peak_hours: peakHours,
        total_revenue: data.reduce((sum, row) => sum + parseFloat(row.hourly_revenue || 0), 0),
        total_orders: data.reduce((sum, row) => sum + parseInt(row.hourly_orders || 0), 0),
        avg_preparation_time: data.length > 0 ? 
          (data.reduce((sum, row) => sum + parseFloat(row.avg_preparation_time || 0), 0) / data.length).toFixed(1) : 0,
        best_hour: peakHours.length > 0 ? peakHours[0].hour : null,
        worst_hour: Object.values(hourlySummary).length > 0 ? 
          Object.values(hourlySummary).sort((a, b) => a.total_revenue - b.total_revenue)[0].hour : null
      };

      return {
        data,
        hourly_summary: Object.values(hourlySummary).sort((a, b) => a.hour - b.hour),
        summary,
        charts: [
          {
            type: 'heatmap',
            title: '24小时销售热力图',
            data: Object.values(hourlySummary).map(hour => ({
              x: hour.hour,
              y: 1,
              v: hour.total_revenue
            }))
          },
          {
            type: 'line',
            title: '小时销售趋势',
            data: Object.values(hourlySummary).map(hour => ({
              x: hour.hour,
              y: hour.total_revenue
            }))
          },
          {
            type: 'bar',
            title: '小时订单量分布',
            data: Object.values(hourlySummary).map(hour => ({
              x: hour.hour,
              y: hour.total_orders
            }))
          }
        ]
      };
    } catch (error) {
      logger.error('生成小时经营分析报表失败:', error);
      throw error;
    }
  }

  // 7. 小时总览 (Hourly Report)
  async getHourlyReport(filters = {}) {
    try {
      const {
        start_date = new Date().toISOString().split('T')[0],
        end_date = new Date().toISOString().split('T')[0],
        store_id
      } = filters;

      let whereClause = 'WHERE DATE(o.created_at) BETWEEN ? AND ?';
      let params = [start_date, end_date];

      if (store_id) {
        whereClause += ' AND o.store_id = ?';
        params.push(store_id);
      }

      const sql = `
        SELECT 
          HOUR(o.created_at) as hour,
          -- 净销售
          SUM(CASE WHEN o.payment_status = 'completed' THEN o.total_amount ELSE 0 END) - 
          SUM(CASE WHEN o.status = 'refunded' THEN o.total_amount ELSE 0 END) as net_sales,
          -- 订单数
          COUNT(CASE WHEN o.payment_status = 'completed' THEN 1 END) as order_count,
          -- 客数 (假设等于订单数)
          COUNT(CASE WHEN o.payment_status = 'completed' THEN 1 END) as customer_count,
          -- 客单价
          AVG(CASE WHEN o.payment_status = 'completed' THEN o.total_amount END) as avg_order_value,
          -- 会员占比
          ROUND(COUNT(CASE WHEN o.payment_status = 'completed' AND o.user_id IS NOT NULL THEN 1 END) * 100.0 / 
                NULLIF(COUNT(CASE WHEN o.payment_status = 'completed' THEN 1 END), 0), 2) as member_ratio,
          -- 渠道结构占比
          ROUND(COUNT(CASE WHEN o.payment_status = 'completed' AND o.order_type = 'dine_in' THEN 1 END) * 100.0 / 
                NULLIF(COUNT(CASE WHEN o.payment_status = 'completed' THEN 1 END), 0), 2) as dine_in_ratio,
          ROUND(COUNT(CASE WHEN o.payment_status = 'completed' AND o.order_type = 'takeaway' THEN 1 END) * 100.0 / 
                NULLIF(COUNT(CASE WHEN o.payment_status = 'completed' THEN 1 END), 0), 2) as takeaway_ratio,
          ROUND(COUNT(CASE WHEN o.payment_status = 'completed' AND o.order_type = 'delivery' THEN 1 END) * 100.0 / 
                NULLIF(COUNT(CASE WHEN o.payment_status = 'completed' THEN 1 END), 0), 2) as delivery_ratio
        FROM orders o
        ${whereClause}
        GROUP BY HOUR(o.created_at)
        ORDER BY hour
      `;

      const data = await db.query(sql, params);

      const summary = {
        total_net_sales: data.reduce((sum, row) => sum + parseFloat(row.net_sales || 0), 0),
        total_orders: data.reduce((sum, row) => sum + parseInt(row.order_count || 0), 0),
        total_customers: data.reduce((sum, row) => sum + parseInt(row.customer_count || 0), 0),
        overall_avg_order_value: 0,
        overall_member_ratio: 0,
        peak_hour: null,
        lowest_hour: null
      };

      if (summary.total_orders > 0) {
        summary.overall_avg_order_value = (summary.total_net_sales / summary.total_orders).toFixed(2);
      }

      // 找出最高和最低销售时段
      if (data.length > 0) {
        const sortedByRevenue = [...data].sort((a, b) => parseFloat(b.net_sales || 0) - parseFloat(a.net_sales || 0));
        summary.peak_hour = sortedByRevenue[0].hour;
        summary.lowest_hour = sortedByRevenue[sortedByRevenue.length - 1].hour;
        
        // 计算整体会员占比
        const totalMemberOrders = data.reduce((sum, row) => {
          const memberOrders = parseFloat(row.member_ratio || 0) * parseInt(row.order_count || 0) / 100;
          return sum + memberOrders;
        }, 0);
        summary.overall_member_ratio = (totalMemberOrders / summary.total_orders * 100).toFixed(2);
      }

      return {
        data,
        summary,
        charts: [
          {
            type: 'area',
            title: '小时销售趋势',
            data: data.map(row => ({
              x: row.hour,
              y: parseFloat(row.net_sales || 0)
            }))
          },
          {
            type: 'line',
            title: '小时会员占比趋势',
            data: data.map(row => ({
              x: row.hour,
              y: parseFloat(row.member_ratio || 0)
            }))
          },
          {
            type: 'stacked_bar',
            title: '小时渠道结构',
            data: data.map(row => ({
              x: row.hour,
              堂食: parseFloat(row.dine_in_ratio || 0),
              外带: parseFloat(row.takeaway_ratio || 0),
              外卖: parseFloat(row.delivery_ratio || 0)
            }))
          }
        ]
      };
    } catch (error) {
      logger.error('生成小时总览报表失败:', error);
      throw error;
    }
  }

  // 8. 销售与客数报表 (Sales And Count)
  async getSalesAndCountReport(filters = {}) {
    try {
      const {
        start_date = new Date().toISOString().split('T')[0],
        end_date = new Date().toISOString().split('T')[0],
        store_id,
        group_by = 'date' // date, hour, order_type
      } = filters;

      let whereClause = 'WHERE DATE(o.created_at) BETWEEN ? AND ?';
      let params = [start_date, end_date];
      let groupByClause = 'DATE(o.created_at)';
      let selectClause = 'DATE(o.created_at) as period';

      if (store_id) {
        whereClause += ' AND o.store_id = ?';
        params.push(store_id);
      }

      // 根据分组方式调整SQL
      switch (group_by) {
        case 'hour':
          groupByClause = 'HOUR(o.created_at)';
          selectClause = 'HOUR(o.created_at) as period';
          break;
        case 'order_type':
          groupByClause = 'o.order_type';
          selectClause = 'o.order_type as period';
          break;
        default:
          // 保持默认的按日期分组
          break;
      }

      const sql = `
        SELECT 
          ${selectClause},
          -- 销售额
          SUM(CASE WHEN o.payment_status = 'completed' THEN o.total_amount ELSE 0 END) as sales_amount,
          -- 订单数
          COUNT(CASE WHEN o.payment_status = 'completed' THEN 1 END) as order_count,
          -- 客数 (假设等于订单数，实际应该考虑多人订单)
          COUNT(CASE WHEN o.payment_status = 'completed' THEN 1 END) as customer_count,
          -- 客单价
          AVG(CASE WHEN o.payment_status = 'completed' THEN o.total_amount END) as avg_order_value,
          -- 人均消费 (假设等于客单价)
          AVG(CASE WHEN o.payment_status = 'completed' THEN o.total_amount END) as avg_per_customer,
          -- 翻台率 (模拟数据，假设堂食订单的翻台率)
          CASE 
            WHEN COUNT(CASE WHEN o.payment_status = 'completed' AND o.order_type = 'dine_in' THEN 1 END) > 0
            THEN ROUND(COUNT(CASE WHEN o.payment_status = 'completed' AND o.order_type = 'dine_in' THEN 1 END) / 20.0, 2)
            ELSE 0
          END as table_turnover_rate,
          -- 会员转化率 (Guest → Member，模拟数据)
          ROUND(COUNT(CASE WHEN o.payment_status = 'completed' AND o.user_id IS NOT NULL THEN 1 END) * 100.0 / 
                NULLIF(COUNT(CASE WHEN o.payment_status = 'completed' THEN 1 END), 0), 2) as member_conversion_rate,
          -- 会员订单数
          COUNT(CASE WHEN o.payment_status = 'completed' AND o.user_id IS NOT NULL THEN 1 END) as member_orders,
          -- 游客订单数
          COUNT(CASE WHEN o.payment_status = 'completed' AND o.user_id IS NULL THEN 1 END) as guest_orders,
          -- 各渠道订单数
          COUNT(CASE WHEN o.payment_status = 'completed' AND o.order_type = 'dine_in' THEN 1 END) as dine_in_orders,
          COUNT(CASE WHEN o.payment_status = 'completed' AND o.order_type = 'takeaway' THEN 1 END) as takeaway_orders,
          COUNT(CASE WHEN o.payment_status = 'completed' AND o.order_type = 'delivery' THEN 1 END) as delivery_orders
        FROM orders o
        ${whereClause}
        GROUP BY ${groupByClause}
        ORDER BY ${group_by === 'hour' ? 'period' : (group_by === 'order_type' ? 'sales_amount DESC' : 'period DESC')}
      `;

      const data = await db.query(sql, params);

      // 计算会员转化漏斗数据
      const conversionFunnel = {
        total_visitors: data.reduce((sum, row) => sum + parseInt(row.customer_count || 0), 0), // 总访客
        total_orders: data.reduce((sum, row) => sum + parseInt(row.order_count || 0), 0), // 总订单
        member_orders: data.reduce((sum, row) => sum + parseInt(row.member_orders || 0), 0), // 会员订单
        guest_orders: data.reduce((sum, row) => sum + parseInt(row.guest_orders || 0), 0), // 游客订单
        order_conversion_rate: 0, // 下单转化率
        member_conversion_rate: 0 // 会员转化率
      };

      if (conversionFunnel.total_visitors > 0) {
        conversionFunnel.order_conversion_rate = (conversionFunnel.total_orders / conversionFunnel.total_visitors * 100).toFixed(2);
      }
      if (conversionFunnel.total_orders > 0) {
        conversionFunnel.member_conversion_rate = (conversionFunnel.member_orders / conversionFunnel.total_orders * 100).toFixed(2);
      }

      const summary = {
        total_sales: data.reduce((sum, row) => sum + parseFloat(row.sales_amount || 0), 0),
        total_orders: data.reduce((sum, row) => sum + parseInt(row.order_count || 0), 0),
        total_customers: data.reduce((sum, row) => sum + parseInt(row.customer_count || 0), 0),
        overall_avg_order_value: 0,
        overall_avg_per_customer: 0,
        overall_table_turnover: 0,
        conversion_funnel: conversionFunnel
      };

      if (summary.total_orders > 0) {
        summary.overall_avg_order_value = (summary.total_sales / summary.total_orders).toFixed(2);
      }
      if (summary.total_customers > 0) {
        summary.overall_avg_per_customer = (summary.total_sales / summary.total_customers).toFixed(2);
      }
      if (data.length > 0) {
        summary.overall_table_turnover = (data.reduce((sum, row) => sum + parseFloat(row.table_turnover_rate || 0), 0) / data.length).toFixed(2);
      }

      return {
        data,
        summary,
        charts: [
          {
            type: 'scatter',
            title: '销售额 vs 客数关系',
            data: data.map(row => ({
              x: parseInt(row.customer_count || 0),
              y: parseFloat(row.sales_amount || 0),
              label: row.period
            }))
          },
          {
            type: 'funnel',
            title: '会员转化漏斗',
            data: [
              { name: '总访客', value: conversionFunnel.total_visitors },
              { name: '下单客户', value: conversionFunnel.total_orders },
              { name: '会员客户', value: conversionFunnel.member_orders }
            ]
          },
          {
            type: 'line',
            title: '客单价趋势',
            data: data.map(row => ({
              x: row.period,
              y: parseFloat(row.avg_order_value || 0)
            }))
          }
        ]
      };
    } catch (error) {
      logger.error('生成销售与客数报表失败:', error);
      throw error;
    }
  }
}

module.exports = new SalesReportController();